package com.example.demo.DAO;

import com.example.demo.Entity.User;
import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    public boolean save(User user) {
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        String sql = "insert into tb_users(USER_LOGNAME, USER_PWD, USER_REALNAME, USER_EMAIL, USER_ROLE, USER_STATE) values(?, ?, ?, ?, ?, ?)";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getUserLogname());
            pstmt.setString(2, user.getUserPwd());
            pstmt.setString(3, user.getUserRealname());
            pstmt.setString(4, user.getUserEmail());
            pstmt.setInt(5, user.getUserRole());
            pstmt.setInt(6, user.getUserState());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public User login(String userLogname, String userPwd) {
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        User u = null;
        String sql = "select USER_ID, USER_REALNAME, USER_EMAIL, USER_ROLE, USER_STATE from tb_users where USER_LOGNAME = ? and USER_PWD = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userLogname);
            pstmt.setString(2, userPwd);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                u = new User();
                u.setUserId(rs.getInt(1));
                u.setUserRealname(rs.getString(2));
                u.setUserEmail(rs.getString(3));
                u.setUserRole(rs.getInt(4));
                u.setUserState(rs.getInt(5));
                u.setUserLogname(userLogname);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return u;


    }

    public int getRecordCount() {
        int recordCount = 0;
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT count(*) FROM tb_users";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next())
                recordCount = rs.getInt(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeJDBC(rs, pstmt, conn);
        }
        return recordCount;

    }

    public List<User> getUserPageList(int pageNo, int pageSize) {
        // 定义本页记录索引值
        int firstIndex = pageSize * (pageNo - 1);
        List<User> list = new ArrayList<User>();
        Connection connection = DBUtil.getConnection();
        if (connection == null)
            return null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = connection
                    .prepareStatement("SELECT user_id, user_logname, user_realname, user_email, user_role, user_state " +
                            "FROM tb_users ORDER BY user_id DESC LIMIT ?, ?");
            pstmt.setInt(1, firstIndex);
            pstmt.setInt(2, firstIndex + pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                User u = new User();
                u.setUserId(rs.getInt(1));
                u.setUserLogname(rs.getString(2));
                u.setUserRealname(rs.getString(3));
                u.setUserEmail(rs.getString(4));
                u.setUserRole(rs.getInt(5));
                u.setUserState(rs.getInt(6));
                list.add(u);

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeJDBC(rs, pstmt, connection);
        }
        return list;
    }

    public User selectById(int uid) {
        User u = new User();
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        String sql = "select user_id, user_logname, user_pwd, user_realname, user_email, user_role, user_state from tb_users where user_id = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, uid);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                u.setUserId(rs.getInt(1));
                u.setUserLogname(rs.getString(2));
                u.setUserPwd(rs.getString(3));
                u.setUserRealname(rs.getString(4));
                u.setUserEmail(rs.getString(5));
                u.setUserRole(rs.getInt(6));
                u.setUserState(rs.getInt(7));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return u;

    }

    public void update(User user) {
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        String sql = "UPDATE tb_users SET USER_LOGNAME = ?, USER_REALNAME = ?, " +
                "USER_EMAIL = ?, USER_ROLE = ?, USER_STATE = ? WHERE USER_ID = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getUserLogname());
            pstmt.setString(2, user.getUserRealname());
            pstmt.setString(3, user.getUserEmail());
            pstmt.setInt(4, user.getUserRole());
            pstmt.setInt(5, user.getUserState());
            pstmt.setInt(6, user.getUserId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeJDBC(null, pstmt, conn);
        }

    }
}
